package com.u.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.mysql.jdbc.PreparedStatement;
import com.u.character.Role;
import com.u.tool.ExcelUtil;
import com.u.tool.MysqlUtil;

/**
 * Servlet implementation class SuperRootServlet
 */
@WebServlet("/SuperRootServlet")
public class SuperRootServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SuperRootServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		System.out.println("test");
		String method = request.getParameter("method");
		if((method == null) || method == "") {
			method = (String) request.getAttribute("method");
		}
		HttpSession httpSession = request.getSession();
		Role role = (Role) httpSession.getAttribute("Role");
		if(!role.getUseracc().equals("21000")) return;
		RequestDispatcher requestDispatcher;
		if(method.equals("getUserList")) {
			String filename = getUserListExcel();
			request.setAttribute("filename", filename);
			requestDispatcher = request.getRequestDispatcher("/DownloadFile");
			requestDispatcher.forward(request, response);
		}else if(method.equals("uploadUserList")) {
			String filename = (String) request.getAttribute("filename");
			System.out.println(filename);
			try {
				setNewUserList(filename);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		response.sendRedirect("/r/eventList.html");
	}
	
	private String getUserListExcel(){
		String filePath = getServletContext().getRealPath("store");
		String filename = "userList.xls";
		ExcelUtil excelUtil = new ExcelUtil(filePath + "\\" + filename);
		MysqlUtil db = new MysqlUtil();
		db.connection();
		String sql = "select * from y_r.role";
		List<Role> list = db.query(Role.class, sql);
		try {
			excelUtil.add(Role.class, list);
			return  filename;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			return "none";
		}	
	}
	
	private void setNewUserList(String filename) throws Exception {
		Role role;
		MysqlUtil db = new MysqlUtil();
		String sql = "truncate table y_r.role";
		db.connection();
		db.pst = (PreparedStatement) db.conn.prepareStatement(sql);
		db.pst.executeUpdate();
		db.close();
		ExcelUtil excelUtil = new ExcelUtil(filename);
		List<Role> roleList = excelUtil.query(Role.class);
		for(int i=0;i<roleList.size();i++) {
			role = roleList.get(i);
			db.connection();
			db.add(role);
		}
	}

}
